﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlTypes;
using MySql.Data.MySqlClient;
namespace Region
{
    public class Procuror : DBObject    
    {
        /// <summary> Идентификатор прокурорской проверки </summary>
        public Int32 ProcurorID { get { return (Int32)getPole("ProcurorID"); } set { setPole("ProcurorID", value); } }
        /// <summary> Исполнитель </summary>
        public Int32 IspUserID { get { return (Int32)getPole("IspUserID"); } set { setPole("IspUserID", value); } }
        /// <summary> Отдел </summary>
        public Int32 OtdelID { get { return (Int32)getPole("OtdelID"); } set { setPole("OtdelID", value); } }
        /// <summary> Номер проверки </summary>
        public Int32 ProcurorNum { get { return (Int32)getPole("ProcurorNum"); } set { setPole("ProcurorNum", value); } }
        /// <summary> Год виписки </summary>
        public Int32 ProcurorYear { get { return (Int32)getPole("ProcurorYear"); } set { setPole("ProcurorYear", value); } }
        /// <summary> Количество нарушений </summary>
        public Int32 NumNar { get { return (Int32)getPole("NumNar"); } set { setPole("NumNar", value); } }
        /// <summary>Дата начала проверки</summary>
        public DateTime Start { get { return (DateTime)getPole("Start"); } set { setPole("Start", value); } }
        /// <summary>Дата окончания проверки</summary>
        public DateTime End { get { return (DateTime)getPole("End"); } set { setPole("End", value); } }
        /// <summary> Коментарий </summary>
        public String Comment { get { return (String)getPole("Comment"); } set { setPole("Comment", value); } }
        public Procuror(Engine engine, Int32 ProcurorID)
            : base(engine, "procuror", "ProcurorID", ProcurorID)
        {
            addPoles(EPoleType.Bool, new String[] {  });
            addPoles(EPoleType.Int64, new String[] { });
            addPoles(EPoleType.Int32, new String[] { "ProcurorID","IspUserID","OtdelID"});
            addPoles(EPoleType.Int32, new String[] { "ProcurorNum","ProcurorYear","NumNar" });
            addPoles(EPoleType.RegionDate, new String[] { "Start","End" });
            addPoles(EPoleType.String, new String[] { "Comment" });           
        }
        List<String> checkProcuror()
        {
            List<String> list = new List<string>();
            return list;
        }
        public new Object getPole(String PoleName) { return base.getPole(PoleName); }
        static private Dictionary<String, bool> Access_NoEdit = new Dictionary<String, bool>() { { "ProcurorID", true } };
        static private Dictionary<String, bool> Access_BasePoles = new Dictionary<String, bool>() {  };
        public override bool poleCheck(String PoleName, Object Value) { return true; }
        public override bool poleAccess(String PoleName)
        {            
            //Не редактируемые поля
            if (Access_NoEdit.ContainsKey(PoleName)) return false;
            // Администратору вообще многое можно           
            if (m_engine.isAccess("Admin")) return true;
            //Основные поля распоряжения
            if (Access_BasePoles.ContainsKey(PoleName))
            {
                return true;
            }
            //Разрешить исполнитулю редактировать поля если они не запрещены правилами выше
            if (IspUserID == m_engine.CurrentUser.UserID) return true;
            return false;//Нет явынх править занчит надо запретить            
        }
        /*
        public override void showEditor()
        {
            var F = new Forms.ProcurorForm(m_engine, ProcurorID);
            F.ShowDialog();
        }*/
    }
    public class ProcurorList
    {
        public struct Line
        {
            public Int32 ProcurorID;
            public Int32 ProcurorNum;
            public DateTime Start;
            public String IspFIO;
            public String Comment;
        }
        private Engine m_engine;
        private List<Line> m_lines;
        private Dictionary<String, Object> m_wheres;
        public Line getLine(Int32 l) { return this.m_lines[l]; }
        public Int32 NumLines { get { return m_lines.Count; } }
        /// <summary> Возвращает список исполнителей у которых есть хотя б одно распоряжение</summary><param name="IspUserFIO">Список фамилий</param><param name="IspUserID">Список идентификаторов</param>
        Dictionary<Int32, Dictionary<Int32, String>> m_IspUserList = new Dictionary<Int32, Dictionary<Int32, String>>();
        public Dictionary<Int32,String> IspUserList(Int32 ParentOtdelID)
        {
            if (m_IspUserList.ContainsKey(ParentOtdelID)) return m_IspUserList[ParentOtdelID];
            Dictionary<Int32, String> R = new Dictionary<Int32, String>();
            List<String> FIO = new List<String>();
            List<Int32> ID = new List<Int32>();
            /*SQLResult r = m_engine.SQLSelect(@"
SELECT procuror.IspUserID, FIO 
FROM procuror 
  LEFT JOIN users ON users.UserID = procuror.IspUserID 
  INNER JOIN otdel_otdel ON otdel_otdel.OtdelID=procuror.OtdelID
WHERE  otdel_otdel.ParentOtdelID=@ParentOtdelID
GROUP BY procuror.IspUserID", new SQLParam() { { "@ParentOtdelID", ParentOtdelID } });*/
            SQLResult r = m_engine.SQLSelect(@"
SELECT users.UserID,users.FIO
FROM users
  LEFT JOIN procuror ON procuror.IspUserID = users.UserID  
  INNER JOIN otdel_otdel ON otdel_otdel.OtdelID=procuror.OtdelID
WHERE  otdel_otdel.ParentOtdelID=@ParentOtdelID AND procuror.ProcurorID IS NOT NULL
GROUP BY users.UserID", new SQLParam() { { "@ParentOtdelID", ParentOtdelID }, { "@UserID", m_engine.CurrentUser.UserID } });
            for (int i = 0; i < r.Count; i++)
                R[(Int32)r[i]["UserID"]] = (String)r[i]["FIO"];
            m_IspUserList[ParentOtdelID] = R;
            return R;

        }
        /// <summary>Возмращает минимальный и максимальный год выписки распоряжений</summary><param name="MinYear"></param><param name="MaxYear"></param>
        public void MinMaxProcurorYear(out Int32 MinYear, out Int32 MaxYear)
        {
            SQLResult r = m_engine.SQLSelect("SELECT MIN(ProcurorYear) AS MinYear, MAX(ProcurorYear) AS MaxYear FROM procuror WHERE 1");
            if (r.Count == 1)
            {
                MinYear = (Int32)r[0]["MinYear"];
                MaxYear = (Int32)r[0]["MaxYear"];
            }
            else
            {
                MinYear = DateTime.Now.Year;
                MaxYear = DateTime.Now.Year;
            }
        }

        public ProcurorList(Engine engine)
        {
            m_engine = engine;
            m_lines = new List<Line>();
            m_wheres = new Dictionary<String, Object>();
        }
        public bool clearWhere()
        {
            m_wheres = new Dictionary<String, Object>();
            return true;
        }
        public bool setWhere(String Where, Object Value)
        {
            m_wheres[Where] = Value;
            return true;
        }
        public bool Filter()
        {
            List<String> Tables = new List<String>();
            List<String> Poles = new List<String>();
            List<String> Wheres = new List<String>();
            List<String> OrderBy = new List<String>();
            SQLParam Params = new SQLParam();

            //Params["@Param"]=100;
            Poles.Add("procuror.ProcurorID");
            Poles.Add("procuror.ProcurorNum");
            Poles.Add("procuror.Start");
            Poles.Add("isp.FIO AS IspFIO");            
            Tables.Add("procuror");            
            Tables.Add("LEFT JOIN users AS isp ON isp.UserID=procuror.IspUserID");
            Wheres.Add("1");
            OrderBy.Add("procuror.ProcurorNum DESC");


            Int32 ProcurorID, ParentOtdelID, ProcurorYear, ProcurorNumStart, ProcurorNumEnd, IspUserID, LineInPage, Page;
            String[] DatePoles = { "Start", "End"};
            for (Int32 i = 0; i < DatePoles.Length; i++)
            {
                String Pole = DatePoles[i];
                if (m_wheres.ContainsKey(Pole + "Start") && m_wheres[Pole + "Start"] is DateTime)
                {
                    Wheres.Add("procuror." + Pole + " >= @" + Pole + "Start");
                    Params["@" + Pole + "Start"] = Engine.RegionDateToInt((DateTime)m_wheres[Pole + "Start"]);
                }
                if (m_wheres.ContainsKey(Pole + "End") && m_wheres[Pole + "End"] is DateTime)
                {
                    Wheres.Add("procuror." + Pole + " < @" + Pole + "End");
                    Params["@" + Pole + "End"] = Engine.RegionDateToInt((DateTime)m_wheres[Pole + "End"]);
                }
            }
            if (m_wheres.ContainsKey("ProcurorID") && m_wheres["ProcurorID"] != null && Int32.TryParse(m_wheres["ProcurorID"].ToString(), out ProcurorID))
            {
                Wheres.Add("procuror.ProcurorID=@ProcurorID");
                Params["@ProcurorID"] = ProcurorID;
            }
            if (m_wheres.ContainsKey("ParentOtdelID") && m_wheres["ParentOtdelID"] != null && Int32.TryParse(m_wheres["ParentOtdelID"].ToString(), out ParentOtdelID))
            {
                Tables.Add("INNER JOIN otdel_otdel ON otdel_otdel.OtdelID=procuror.OtdelID");
                Wheres.Add("otdel_otdel.ParentOtdelID=@ParentOtdelID");
                Params["@ParentOtdelID"] = ParentOtdelID;
            }
            if (m_wheres.ContainsKey("ProcurorYear") && m_wheres["ProcurorYear"] != null && Int32.TryParse(m_wheres["ProcurorYear"].ToString(), out ProcurorYear))
            {
                Wheres.Add("procuror.ProcurorYear=@ProcurorYear");
                Params["@ProcurorYear"] = ProcurorYear;
            }

            if (m_wheres.ContainsKey("ProcurorNumStart") && m_wheres["ProcurorNumStart"] != null && Int32.TryParse(m_wheres["ProcurorNumStart"].ToString(), out ProcurorNumStart) && ProcurorNumStart > 0)
            {
                Wheres.Add("procuror.ProcurorNum>=@ProcurorNumStart");
                Params["@ProcurorNumStart"] = ProcurorNumStart;
            }
            if (m_wheres.ContainsKey("ProcurorNumEnd") && m_wheres["ProcurorNumEnd"] != null && Int32.TryParse(m_wheres["ProcurorNumEnd"].ToString(), out ProcurorNumEnd) && ProcurorNumEnd > 0)
            {
                Wheres.Add("procuror.ProcurorNum<=@ProcurorNumEnd");
                Params["@ProcurorNumEnd"] = ProcurorNumEnd;
            }
            if (m_wheres.ContainsKey("IspUserID") && m_wheres["IspUserID"] != null && Int32.TryParse(m_wheres["IspUserID"].ToString(), out IspUserID))
            {
                Wheres.Add("procuror.IspUserID=@IspUserID");
                Params["@IspUserID"] = IspUserID;
            }


            String OrderBySQL = "";
            String LimitSQL = "";
            if (m_wheres.ContainsKey("LineInPage") && m_wheres["LineInPage"] != null && Int32.TryParse(m_wheres["LineInPage"].ToString(), out LineInPage) && LineInPage > 1)
            {
                if (!m_wheres.ContainsKey("Page") || m_wheres["Page"] == null || !Int32.TryParse(m_wheres["Page"].ToString(), out Page)) Page = 1;
                Int32 Start = (Page - 1) * LineInPage;
                LimitSQL = "LIMIT " + Start + ", " + LineInPage + " ";
            }

            if (OrderBy.Count > 0) OrderBySQL = "ORDER BY " + String.Join(", ", OrderBy.ToArray());
            String SQL = String.Format("SELECT {0} FROM {1} WHERE ({2}) {3} {4}",
                String.Join(", ", Poles.ToArray()),
                String.Join(" ", Tables.ToArray()),
                String.Join(") AND (", Wheres.ToArray()),
                OrderBySQL, LimitSQL);
            SQLResult r = m_engine.SQLSelect(SQL, Params);
            m_lines.Clear();
            for (Int32 i = 0; i < r.Count; i++)
            {
                Line l = new Line();
                l.ProcurorID = (Int32)r[i]["ProcurorID"];
                l.ProcurorNum = (Int32)r[i]["ProcurorNum"];
                try { l.IspFIO = Engine.RegionStringToString((String)r[i]["IspFIO"]); }
                catch { l.IspFIO = ""; }
                l.Start = Engine.IntToRegionDate((Int32)r[i]["Start"]);
                m_lines.Add(l);
            }

            return false;
        }
    }
    public partial class Engine
    {
        private Dictionary<Int32, Procuror> l_Procuror;
        /// <summary>Получить прокурорскую проверку</summary><param name="ProcurorID"></param><returns></returns>
        public Procuror getProcuror(Int32 ProcurorID)
        {
            if (l_Procuror == null) l_Procuror = new Dictionary<int, Procuror>();
            if (l_Procuror.ContainsKey(ProcurorID)) return (Procuror)l_Procuror[ProcurorID];
            Procuror procuror = new Procuror(this, ProcurorID);
            l_Procuror[ProcurorID] = procuror;
            return procuror;
        }
        /// <summary>[НЕПРОВЕРЕНА] Создать новую прокуроскую проверку </summary><param name="BaseProcurorID"></param><returns></returns>
        public Int32 newProcuror(Int32 BaseProcurorID)
        {
            SQLParam Param = new SQLParam();
            Param["@ProcurorYear"] = DateTime.Now.Year;
            Param["@Date"] = RegionDateToInt(DateTime.Now);
            Param["@IspUserID"] = CurrentUser.UserID;
            Param["@OtdelID"] = CurrentUser.OtdelID;
            // Поля копируеы при создании на основе
            String[] Poles = new String[] { "Comment" };
            String[] SqlPoles = new String[0];
            String SqlPolesN = "";
            //foreach (String Pole in Poles) Param["@" + Pole] = "";

            if (BaseProcurorID > 0)
            {
                Procuror procuror = getProcuror(BaseProcurorID);
                SqlPoles = Poles;
                foreach (String Pole in Poles)
                {
                    Param["@" + Pole] = procuror.getPole(Pole);
                    SqlPolesN += ", @" + Pole + " AS " + Pole;
                }
            }
            MySqlCommand cmd;
            SQLEdit(@"
INSERT INTO procuror(ProcurorNum,ProcurorYear,Date,IspUserID,OtdelID " + ((SqlPoles.Length > 0) ? ", " : "") + String.Join(" ,", SqlPoles) + @") 
SELECT R1.ProcurorNum+1 AS ProcurorNum, @ProcurorYear AS ProcurorYear, @Date AS Date, 
  @IspUserID AS IspUserID, @OtdelID AS OtdelID " + SqlPolesN + @"
FROM procuror AS R1 
  LEFT JOIN procuror AS R2 ON (R2.ProcurorNum=R1.ProcurorNum+1 AND R2.ProcurorYear=@ProcurorYear ) 
WHERE R2.ProcurorNum IS NULL AND R1.ProcurorYear=@ProcurorYear 
ORDER BY R1.ProcurorNum 
LIMIT 1", Param, out cmd);
            return (Int32)cmd.LastInsertedId;            
        }
        /// <summary>[НЕПРОВЕРЕНА] Удалить прокурорскую проверку</summary><param name="ProcurorID">ИД проверки</param><returns>true - проверка удалена</returns>
        public bool delProcuror(Int32 ProcurorID)
        {
            Procuror procuror = getProcuror(ProcurorID);
            if (!CurrentUser.isAccess("EditRaspor")) return false;// Пользователь неимеет права удалять распоряжения            
            if (!CurrentUser.isAccess("Admin") && CurrentUser.OtdelID != procuror.OtdelID && !Otdels[CurrentUser.OtdelID].isSubOtdel(procuror.OtdelID)) return false;//нельзя удалить черновик другово отдела
            SQLParam Param = new SQLParam();
            Param["ProcurorID"] = ProcurorID;
            //Удалить само распоряжение
            Int32 N = SQLEdit("DELETE FROM procuror WHERE ProcurorID=@ProcurorID", Param);            
            if (N == 1) // Проверка действительно удалено. 
            {
                l_Procuror.Remove(ProcurorID);                                
                return true;
            }
            return false;

        }
    }

}